CREATE DATABASE CourierManagement
GO
USE CourierManagement
GO

CREATE TABLE Department
(
	DepId int identity primary key,
	DepartmentName nvarchar(50),
	Description nvarchar(200),
	Role int -- 1: la phong ban; --2: la kho; -- 3: Van tai
)

CREATE TABLE Employee
(
	EmpId int identity primary key,
	EmployeeName nvarchar(50),
	Address nvarchar(200),
	Email nvarchar(50),
	Phone nvarchar(12),
	Image nvarchar(200),
	Position nvarchar(20),
	DepId int foreign key references Department
)


CREATE TABLE EmployeeLogin
(
	EmpId int primary key references Employee,
	Username nvarchar(50),
	Password nvarchar(50),
	Alias nvarchar(10),
	AliasPassword nvarchar(10),
	[Status] bit default 0,
	StandbyTime nvarchar(20),
	StandbyResume nvarchar(20),
)

CREATE TABLE Attendance
(
	EmpId int foreign key references EmployeeLogin,
	LoginTime nvarchar(20)
)

CREATE TABLE Courier
(
	CourierId int identity primary key,
	CourierName nvarchar(200),
	CustomerName nvarchar(200),
	ReceivingCustomer nvarchar(200),
	Phone nvarchar(12),
	Status nvarchar(max),
	Weight nvarchar(20),
	DepartLocation nvarchar(200),
	Destination nvarchar(200),
	Distance nvarchar(10)
)

CREATE TABLE CourierManage
(
	CourierId int foreign key references Courier,
	Sender int foreign key references Employee(EmpId),
	Receiver int foreign key references Employee(EmpId),
	Dispatcher int foreign key references Employee(EmpId),
	Status int,
	CurrentPosition tinyint null,
	Description nvarchar(max), -- Dua len giao dien phai la Ly do hoan, thay doi hoac khong gui duoc hang 
	UpdateTime nvarchar(20)
)

CREATE TABLE Administrator
(
	AdId int identity primary key,
	AdUser nvarchar(50),
	AdPass nvarchar(50),
	Role char,
	DepId int foreign key references Department
)

CREATE TABLE Calculation
(
	Id int identity primary key,
	Distance nvarchar(10),
	ChargePerGram real
)

CREATE TABLE City
(
	CityId int identity primary key,
	CityName nvarchar(50)
)

CREATE TABLE District
(
	DistrictId int identity primary key,
	DistrictName nvarchar(50),
	CityId int foreign key references City
)

Insert into Department values('Phong Gui - Nhan', 'Phong Gui - Nhan', '1') --1 PHONG BAN
Insert into Department values('Phong Dieu Van', 'Phong Dieu Van', '2') --3: Admin
Insert into Department values('Kho Hang 1', 'Kho Hang 1', '3') --2: KHO
Insert into Department values('Kho Hang 2', 'Kho Hang 2', '3')
Insert into Department values('Kho Hang 3', 'Kho Hang 3', '3')
Insert into Department values('Administrator', 'Administrator', '4')

Insert into Employee values('0', '0', '0','0','0','0','1')
Insert into Employee values('Nhan Vien Nhan Dau', 'HN', 'anv@gmail.com','0986999999','nguyenvana.jpg','nhan vien','1')
Insert into Employee values('Nguoi Van Chuyen', 'HD', 'bnv@gmail.com','0986999999','nguyenvanb.jpg','nhan vien','2')
Insert into Employee values('NV Kho Nguon', 'BD', 'cnv@gmail.com','0986999999','nguyenvanc.jpg','nhan vien','3')
Insert into Employee values('NV Kho Dich', 'BD', 'kho@gmail.com','0986999999','Nguyenvankho.jpg','nhan vien kho','3')
Insert into Employee values('Nguoi Nhan Cuoi', 'BD', 'kho@gmail.com','0986999999','Nguyenvankho.jpg','nhan vien kho','1')

Insert into EmployeeLogin values ('2', 'NV Nhan Dau','123456','Alias_A','123456','0', '','')
Insert into EmployeeLogin values ('3', 'Nguoi Van Chuyen','123456','Alias_B','123456','0', '','')
Insert into EmployeeLogin values ('4', 'NV Kho Nguon','123456','Alias_B','123456','0', '','')
Insert into EmployeeLogin values ('5', 'NV Kho Dich','123456','Alias_B','123456','0', '','')
Insert into EmployeeLogin values ('6', 'Nguoi Nhan Cuoi','123456','Alias_B','123456','0', '','')

Insert into Courier values('Hang 1','Khach hang 1','098123456','','500','Ha Noi','Hai Duong','60')
Insert into Courier values('Hang 2','Khach hang 2','098123456','','300','Hai Duong','Ha Giang','120')


Insert into Calculation values('1','10000')
Insert into Calculation values('10','10000')

Insert into City values('Ha Noi')
Insert into City values('Hai Phong')
Insert into City values('Hai Duong')

Insert into District values('Long Bien','1')
Insert into District values('Ba Dinh','1')
Insert into District values('Le Chan','2')
Insert into District values('Kien An','2')
Insert into District values('Chi Linh','3')
Insert into District values('Cam Giang','3')

select * from CourierManage

-- Nhan hang tu khach hang
Insert into Courier values('Qua Tang','Nguoi Gui 1','098612345','Received From Customers','3','HN','HP','60') --Noi voi bang CourierManage: Cai Status: phai dung combobox de chon
Insert into CourierManage values('3','1','2','1','1','0','Received From Customers','3/11/2014') --1: la Received From Customers
-- Gui hang vao kho: Do nguoi nhan dau tien tu khach hang gui cho nhan vien kho.
Insert into CourierManage values('3','2','4','1','2','0','Send To First Store','3/11/2014')-- 2: la gui hang vao kho: Send To Store
--NHAN VIEN KHO 1 XAC NHAN DE NHAN HANG VAO KHO
Insert into CourierManage values('3','2','4','1','6','1','Courier In Store','3/11/2014')-- 6: Hang da vao kho: Courier In Store va CSDL cua nhan vien gui vao cung update Status + Nguoi nhan len 6
-- Gui hang tu kho di van chuyen: Do bo phan nhan hang den kho nhan.
Insert into CourierManage values ('3','4','3','1','3','0','Send To Despatching Employee','3/12/2014') -- 3: la gui hang cho nguoi van chuyen: Send To Despatching Department (Tat ca cac nhan vien Despatching deu nhan duoc thong tin)
--NGUOI VAN CHUYEN XAC NHAN DE NHAN HANG
Insert into CourierManage values ('3','4','3','1','7','0','Courier In Despatching','3/12/2014')-- 7: Hang dang van chuyen
-- Gui hang vao kho dich: Do nguoi van chuyen hang mang to kho dich de gui (Sau do gui tra khach hang)
Insert into CourierManage values('3','3','5','1','4','0','Send To Destination Store','3/13/2014') -- 4: Gui hang vao kho dich
-- NHAN VIEN KHO DICH XAC NHAN DA NHAN HANG
Insert into CourierManage values('3','3','5','1','8','0','Courier in Final Store','3/13/2014') -- 8: Hang da vao kho dich
-- Gui hang tu kho dich ra nguoi nhan dau cuoi
Insert into CourierManage values('3','5','6','1','5','0','Send To Final Employee','3/14/2014') -- 5: Gui hang den nguoi nhan cuoi de tra KH-- 2: hang se nhan tu dong nghiep
-- NHAN VIEN DAU CUOI XAC NHAN DA NHAN HANG
Insert into CourierManage values('3','5','6','1','9','0','Final Employee Receive Courier','3/14/2014') -- 9: Nhan vien dau cuoi da nhan hang
-- Tra cho khach hang
Insert into Courier values('Qua Tang','Nguoi Nhan 1','0987655','Return Courier to Customer','0','0','0','0')-- 1: Received From Customers/ 2: Return Courier to Customer
Insert into CourierManage values('3','6','1','1','10','0','Returned To Customers','3/15/2014')--10: Da tra cho KH

-- Hien thi bang nhung hang de tra cho khach hang
Select * from Courier join CourierManage on Courier.CourierId = CourierManage.CourierId where (CourierManage.Status like '9') and CourierManage.CurrentPosition like '1'

Select Courier.CourierId, CourierName, CustomerName, Courier.Phone, Receiver,
            Sender, CourierManage.Status, UpdateTime from Courier join CourierManage 
					on Courier.CourierId = CourierManage.CourierId 
					join Employee on CourierManage.Receiver = Employee.EmpId
					join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId
					where (CourierManage.Status like '3') and CourierManage.CurrentPosition like '1' and Employee.EmpId='6'

Select Department.Role from Department join Employee on Department.DepId = Employee.DepId where EmpId = '2'

Select Courier.CourierId, CourierName, CustomerName, Courier.Phone, Receiver,CourierManage.Status,
            Sender, CourierManage.Status, UpdateTime from Courier join CourierManage
					on Courier.CourierId = CourierManage.CourierId
					join Employee on CourierManage.Receiver = Employee.EmpId
					join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId
					where (CourierManage.Status like '10') and CourierManage.CurrentPosition like '1' and Employee.EmpId='6'

Select Username, EmployeeName, Address, Phone from Employee join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId
															join Department on Employee.DepId = Department.DepId
															where Role = '3'

Select Department.Role from Department join Administrator on Department.DepId = Administrator.DepId where AdId = '1'